/*
package com.itts.middle.modules.poi;

import com.itts.common.util.result.Query;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.support.RequestContext;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

public class exportExcel {
    //https://www.cnblogs.com/wangjianguang/p/7112391.html
    @SuppressWarnings({"deprecation", "unchecked"})
    @RequestMapping("export-TrainHistoryRecord")
    @ResponseBody
    protected void buildExcelDocument(Query query, ModelMap model, SXSSFWorkbook workbook, HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {
        //大数据量导出数据，分页都已实现，但怎样才能去压榨时间，高效导出？
        //SXSSF在必须生成大型电子表格时使用, 堆空间有限
        //官方提供了2种方法

        workbook = new SXSSFWorkbook(100);//值100  在内存中保留100行，超过行将被刷新到磁盘
        //  值-1表示无限制访问.在这种情况下所有,没有被调用flush（）刷新的记录可用, 用于随机访问。
        workbook.setCompressTempFiles(true); //当临时文件过大时，可使用setCompressTempFiles方法进行压缩
        try {
            response.reset();
            // 获得国际化语言
            RequestContext requestContext = new RequestContext(request);
            String CourseCompany = requestContext.getMessage("manage-student-trainRecods");
            response.setContentType("APPLICATION/vnd.ms-excel;charset=UTF-8");
            // 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
            response.setHeader(
                    "Content-Disposition",
                    "attachment; filename="
                            + java.net.URLEncoder.encode(DateUtil.getExcelDate(new Date()) + ".xlsx", "UTF-8"));//Excel 扩展名指定为xlsx  SXSSFWorkbook对象只支持xlsx格式
            OutputStream os = response.getOutputStream();
            CellStyle style = workbook.createCellStyle();


            // 设置样式
            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);//设置单元格着色
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //设置单元格填充样式
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//设置下边框
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//设置左边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);//设置右边框
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
            //获取国际化文件
            String employeeCode = requestContext.getMessage("employeeCode");
            String employeeName = requestContext.getMessage("employeeName");
            String orgName = requestContext.getMessage("orgName");
            String startDate = requestContext.getMessage("start.date");
            String endDate = requestContext.getMessage("end.date");
            String courseCode = requestContext.getMessage("courseCode");
            String courseName = requestContext.getMessage("courseName");
            String sessionName = requestContext.getMessage("sessionName");

            List<T> list = null;
            try {
                //查询数据库中共有多少条数据
                query.setTotal(employeeTrainHistoryService.fetchCountEmployeeTrainHistoryByQuery(query));

                int page_size = 100000;// 定义每页数据数量
                int list_count = query.getTotal();
                //总数量除以每页显示条数等于页数
                int export_times = list_count % page_size > 0 ? list_count / page_size
                        + 1 : list_count / page_size;
                //循环获取产生每页数据
                for (int m = 0; m < export_times; m++) {
                    query.setNeedQueryAll(false);
                    query.setPageSize(100000);//每页显示多少条数据
                    query.setPageNum(m + 1);//设置第几页
                    //查询数据集合
                    list = employeeTrainHistoryService.getEmployeeTrainHistoryByQuery(query);
                    //新建sheet
                    Sheet sheet = null;
                    sheet = workbook.createSheet(System.currentTimeMillis() + CourseCompany + m);
                    // 创建属于上面Sheet的Row，参数0可以是0～65535之间的任何一个，
                    Row header = sheet.createRow(0); // 第0行
                    // 产生标题列，每个sheet页产生一个标题
                    Cell cell;
                    String[] headerArr = new String[]{employeeCode, employeeName,
                            orgName, startDate, endDate, courseCode, courseName, sessionName,
                            hoursNunber};
                    for (int j = 0; j < headerArr.length; j++) {
                        cell = header.createCell((short) j);
                        cell.setCellStyle(style);
                        cell.setCellValue(headerArr[j]);
                    }
                    // 迭代数据
                    if (list != null && list.size() > 0) {
                        int rowNum = 1;
                        for (int i = 0; i < list.size(); i++) {
                            T history = list.get(i);
                            sheet.setDefaultColumnWidth((short) 17);
                            Row row = sheet.createRow(rowNum++);
                            row.createCell((short) 0).setCellValue(
                                    history.getEmployeeCode());
                            row.createCell((short) 1).setCellValue(
                                    history.getEmployeeName());
                            row.createCell((short) 2)
                                    .setCellValue(history.getOrgName());
                            if (history.getTrainBeginTime() != null) {
                                row.createCell((short) 3).setCellValue(
                                        DateUtil.toString(history.getTrainBeginTime()));
                            } else {
                                row.createCell((short) 3).setCellValue("");
                            }
                            if (history.getTrainEndTime() != null) {
                                row.createCell((short) 4).setCellValue(
                                        StringUtils.toString(history.getTrainEndTime()));
                            } else {
                                row.createCell((short) 4).setCellValue("");
                            }
                            row.createCell((short) 5).setCellValue(
                                    history.getCourseCode());
                            row.createCell((short) 6).setCellValue(
                                    history.getCourseName());
                            row.createCell((short) 7).setCellValue(
                                    history.getSessionName());
                            if (history.getHoursNumber() != null)
                                row.createCell((short) 8).setCellValue(
                                        history.getHoursNumber().toString());
                        }
                    }

                    list.clear();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                workbook.write(os);
                os.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
*/
